﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.IO;

using Aspose.Cells;

namespace Best.Framework.Web
{
    public class ExcelHelper
    {

        private static readonly List<string> _fileExtList = new List<string> { 
            ".xls", ".xlsx"
        };

        /// <summary>
        /// 读取Excel表格
        /// </summary>
        /// <param name="excelFilePath">excel文件路径</param>
        /// <param name="firstDataRowIndex">Excel正式数据起始行号</param>
        /// <returns></returns>
        public static DataSet ReadExcelToDataset(string excelFilePath, int firstDataRowIndex = 0)
        {
            if (string.IsNullOrEmpty(excelFilePath))
            {
                throw new ArgumentNullException("参数 excelFilePath 不能为空");
            }
            if (!File.Exists(excelFilePath))
            {
                throw new ArgumentException("文件不存在，请确认路径是否正确");
            }
            string fileExt = Path.GetExtension(excelFilePath);

            if (!_fileExtList.Contains(fileExt))
            {
                throw new ArgumentException("不支持该文件格式");
            }

            DataSet ds = new DataSet();

            Workbook book = new Workbook();
            book.Open(excelFilePath);

            DataTable table;
            //默认取表1
            //foreach (Worksheet sheet in book.Worksheets)
            //{
                Worksheet sheet = book.Worksheets[0];
                table = sheet.Cells.ExportDataTable(firstDataRowIndex
                    , 0, sheet.Cells.MaxDataRow, sheet.Cells.MaxDataColumn,false);
                if (table != null && table.Rows.Count > 0)
                {
                    ds.Tables.Add(table);
                }
           // }
            return ds;
        }

        
    }
}
